
* Set Directory
cd "... Replication Files"

use "Data/BC_PPD.dta", clear

keep if fy >= 2015
drop if fy == 2023
drop if fy == .

replace contrib_ee_other = 0 if contrib_ee_other == .
gen contrib_ee_tot = contrib_ee_regular + contrib_ee_other

rename statename state

replace contrib_er_state = 0 if contrib_er_state == .
replace contrib_er_other = 0 if contrib_er_other == .
gen contrib_er_excess = contrib_er_state + contrib_er_other
replace contrib_er_excess = . if contrib_er_regular == .
replace contrib_er_excess = . if contrib_er_tot == . | contrib_tot == 0

gen ex_missing = contrib_er_excess == .

collapse (sum) contrib_tot contrib_er_tot contrib_er_excess contrib_ee_tot contrib_er_regular contrib_er_state contrib_er_other requiredcontribution ex_missing actassets_gasb actliabilities_gasb uaal_gasb payroll (mean) uaalamortperiod_gasb blendeddiscountrate, by(state govtname fy)

sort govtname fy

gen perc_req = contrib_er_tot / requiredcontribution
gen perc_pay_er = contrib_er_tot / payroll
gen funding_ratio = actassets_gasb / actliabilities_gasb


* Merge in Population Data
merge 1:1 govtname fy using "Data/Population_AllGovs.dta", keepus(population)
drop if _merge == 2
drop _merge

* Merge in Unemployment Data
merge 1:1 govtname fy using "Data/unemployment_allgovs.dta", keepus(percent_unemployed)
drop if _merge == 2
drop _merge

* Merge in SNAP data
merge 1:1 govtname fy using "Data/Snap_allgovs.dta", keepus(snap_hh_share)
drop if _merge == 2
drop _merge

gen state_indicator = state == govtname

* Merge in data on state aid 
merge m:1 state using "Data/cleanedAidRepsPop_all.dta" 
keep if _merge == 3
drop _merge
rename pop_2020 statepop_2020 

* Merge in local aid
merge m:1 govtname using "Data/local_aid.dta"
drop if _merge == 2
drop _merge

* Adjust number of reps for non-state govs
sort govtname fy 
gen pandemic_pop = 0
replace pandemic_pop = population if fy == 2020
by govtname: egen population_2020 = max(pandemic_pop)

** This uses same value for states and cities within a state
gen reps_per_mil_post = 0
replace reps_per_mil_post = (all_reps/(statepop_2020 / 1000000)) if fy == 2021 | fy == 2022

** This uses different values for cities that is based on COngressional geography
gen alt_reps = all_reps
replace alt_reps = reps_adj if reps_adj != .
gen alt_reps_per_mil_post = 0
replace alt_reps_per_mil_post = (alt_reps/(population_2020 / 1000000)) if fy == 2021 | fy == 2022

* Drop DC 
drop if state == "District of Columbia"

gen narrow_aid_21 = state_aid_21
replace narrow_aid_21 = localGovt_aid_21 if state_indicator == 0
gen narrow_aid_22 = state_aid_22 
replace narrow_aid_22 = localGovt_aid_22 if state_indicator == 0

gen wider_aid_21 = state_agency_aid_21
replace wider_aid_21 = local_aid_21 if state_indicator == 0
gen wider_aid_22 = state_agency_aid_22
replace wider_aid_22 = local_aid_22 if state_indicator == 0

gen narrow_aid_post = 0
replace narrow_aid_post = (narrow_aid_21 * 1000000 / population_2020) if fy == 2021
replace narrow_aid_post = (narrow_aid_22 * 1000000 / population_2020) if fy == 2022

gen wider_aid_post = 0
replace wider_aid_post = (wider_aid_21 * 1000000 / population_2020) if fy == 2021
replace wider_aid_post = (wider_aid_22 * 1000000 / population_2020) if fy == 2022


* Merge in CPI
merge m:1 fy using "Data/CPI Price Deflator.dta"
keep if _merge == 3
drop _merge

* Deflate aid using CPI
foreach y in contrib_er_tot contrib_ee_tot contrib_er_excess contrib_er_state requiredcontribution narrow_aid_post wider_aid_post wider_aid_22 { 
	replace `y' = `y' * 255.7 / price_deflator 
}

egen gov_group = group(govtname)
egen state_group = group(state)
xtset gov_group fy

gen excess_pop = contrib_er_excess * 1000 / l.population

foreach var in funding_ratio percent_unemployed snap_hh_share {
	gen l_`var'= l.`var'
}

drop if ex_missing != 0

save "Data/Analytic_sample.dta", replace

